﻿/*
select * from T_Classification
select * from Sys_Module
*/

insert into T_Classification (ParentId,ClassificationCode,ClassificationName)
values(0,'RecordType','记录类型')
insert into T_Classification (ParentId,ClassificationCode,ClassificationName)
values(0,'ProgressType','进度类型')

--添加菜单 
declare @PModuleId int
select @PModuleId=ModuleId from Sys_Module where [ModuleCode]='003'
if(exists(select 1 from Sys_Module where [ModuleCode]='003002'))
begin
  print '【记录类型管理】 已存在'
end
else
begin
	insert into Sys_Module([ModuleName],[ModuleCode],[ParentId],[Valid],[Href],[IsLeaf],[OrderNo],[TreeOrderNo],[SysNo])
	values('记录类型管理','003002',@PModuleId,1,'/Phone/Classification.aspx?Code=RecordType',1,'03','','001')
end

if(exists(select 1 from Sys_Module where [ModuleCode]='003003'))
begin
  print '【进度类型管理】 已存在'
end
else
begin
	insert into Sys_Module([ModuleName],[ModuleCode],[ParentId],[Valid],[Href],[IsLeaf],[OrderNo],[TreeOrderNo],[SysNo])
	values('进度类型管理','003003',@PModuleId,1,'/Phone/Classification.aspx?Code=ProgressType',1,'04','','001')
end

if(exists(select 1 from Sys_Module where [ModuleCode]='003001Super'))
begin
  print '【客户资料高级查询】 已存在'
end
else
begin
	insert into Sys_Module([ModuleName],[ModuleCode],[ParentId],[Valid],[Href],[IsLeaf],[OrderNo],[TreeOrderNo],[SysNo])
	values('客户资料高级查询','003001Super',@PModuleId,1,'/Customer/CustomerList.aspx?U=Super',1,'03','','001')
end

select @PModuleId=ModuleId from Sys_Module where [ModuleCode]='002'
if(exists(select 1 from Sys_Module where [ModuleCode]='002002Add'))
begin
  print '【产品资料录入】 已存在'
end
else
begin
	insert into Sys_Module([ModuleName],[ModuleCode],[ParentId],[Valid],[Href],[IsLeaf],[OrderNo],[TreeOrderNo],[SysNo])
	values('产品资料录入','002002Add',@PModuleId,1,'/Phone/PhoneEdit.aspx?Action=HAdd',1,'01','','001')
end


update Sys_Module set [OrderNo]='02' where [ModuleCode]='002002'
update Sys_Module set [Href]='/Phone/PhoneEdit.aspx?Action=HAdd',[ModuleName]='产品资料录入'
where [ModuleCode]='001001Add'
update Sys_Module set [OrderNo]='10' where [ModuleCode]='002001'

update Sys_Module set [Href]='/Customer/CustomerEdit.aspx?Action=HAdd',[ModuleName]='客户资料录入'
where [ModuleCode]='003001Add'

update Sys_Module set [ModuleName]='客户资料管理'
where [ModuleCode]='003001'

update Sys_Module set [Href]='/Customer/CustomerList.aspx?U=Super'
where [ModuleCode]='003001Super'

--创建表
CREATE TABLE [dbo].[T_ProgressRecord]
(
	[ProgressRecordId] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, 
	[CustomerId] UNIQUEIDENTIFIER NOT NULL,
    [RecordTypeId] INT NULL, 
    [ProgressTypeId] INT NULL, 
    [RecordTime] VARCHAR(20) NULL, 
    [RecordContent] VARCHAR(8000) NULL,
	[CreateTime]         VARCHAR (20)     NULL,
    [CreateUserId]       INT              NULL,
    [UpdateTime]         VARCHAR (20)     NULL,
    [UpdateUserId]       INT              NULL,
    [Valid]              INT              NULL, 
)

--创建视图
CREATE VIEW [dbo].[View_T_ProgressRecord]
AS 
SELECT 
T_ProgressRecord.* 
,CustomerName=Sys_User.UserName
,RecordTypeName=RT.ClassificationName
,ProgressTypeName=PT.ClassificationName
FROM T_ProgressRecord
left outer join Sys_User on Sys_User.UserId=T_ProgressRecord.CreateUserId
left outer join T_Classification as RT on RT.ClassificationId=T_ProgressRecord.RecordTypeId
left outer join T_Classification as PT on PT.ClassificationId=T_ProgressRecord.ProgressTypeId

go


CREATE VIEW [dbo].[View_T_Customer]
AS 
SELECT 
T.* 
,CreateUserName=CTUser.UserName
,UpdateUserName=UTUser.UserName
FROM T_Customer T
left outer join Sys_User as CTUser on T.CreateUserId=CTUser.UserId
left outer join Sys_User as UTUser on T.CreateUserId=UTUser.UserId

go